﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Data.SQLite;

#nullable enable

namespace SimpleORM.Common
{
    /// <summary>
    /// 临时SQLite数据库。
    /// </summary>
    public class TempSQLiteDataBase : IDisposable
    {
        /// <summary>
        /// 表格信息。
        /// </summary>
        private class TableInfo
        {
            /// <summary>
            /// 表格名称。
            /// </summary>
            public string TableName { get; set; } = null!;

            /// <summary>
            /// 表列数。
            /// </summary>
            public int ColumnCount { get; set; } = 0;

            /// <summary>
            /// 表格关联的数据类型。
            /// </summary>
            public Type Type { get; set; } = null!;

            /// <summary>
            /// 行号ID列表，使用它来索引数据库表中的行号。
            /// </summary>
            public List<string> RowIdList { get; set; } = new List<string>();

            /// <summary>
            /// 表行数。
            /// </summary>
            public int RowCount
            {
                get
                {
                    return RowIdList.Count;
                }
            }
        }

        /// <summary>
        /// 临时数据库文件名。
        /// </summary>
        private string _tempDataSource = string.Empty;

        /// <summary>
        /// 数据库连接。
        /// </summary>
        private SQLiteConnection _sqliteConnection;

        /// <summary>
        /// 数据库链接命令。
        /// </summary>
        private SQLiteCommand _sqliteCommand;

        /// <summary>
        /// 表格信息列表。
        /// </summary>
        private List<TableInfo> _tableInfoList = new List<TableInfo>();

        /// <summary>
        /// 创建一个临时SQLite数据库实例。
        /// </summary>
        public TempSQLiteDataBase()
        {
            _sqliteConnection = CreateTempSQLiteConnection();
            _sqliteConnection.Open();
            _sqliteCommand = _sqliteConnection.CreateCommand();

        }

        /// <summary>
        /// 清理资源。
        /// </summary>
        public void Dispose()
        {
            _sqliteConnection.Close();
            _sqliteCommand.Dispose();

            if (File.Exists(_tempDataSource))
            {
                File.Delete(_tempDataSource);
            }
        }

        /// <summary>
        /// 创建临时数据库链接。
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection CreateTempSQLiteConnection()
        {
            _tempDataSource = CreateTempDataSource();

            var sqliteConnection = new SQLiteConnection();
            sqliteConnection.ConnectionString = $"Data Source = {_tempDataSource}";

            return sqliteConnection;
        }

        /// <summary>
        /// 删除指定名字的表格。
        /// </summary>
        /// <param name="tableName">需要删除的表格名称。</param>
        public void DeleteTableByName(string tableName)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            string commandString = $"DROP TABLE '{tableName}';";

            ExecuteAction(commandString);

            _tableInfoList.Remove(tableInfo);
        }

        /// <summary>
        /// 根据一个表格，创建与数据类型对应的表格。暂时只支持Double,int,bool类型的属性。
        /// </summary>
        /// <typeparam name="TDataType">数据类型。</typeparam>
        /// <param name="tableName">需要创建表格的名字。</param>
        public void CreateTableByType<TDataType>(string tableName)
        {
            if (GetTableInfo(tableName) is not null)
            {
                throw new Exception($"表格{tableName}已经存在。");
            }

            Type? type = typeof(TDataType);

            var properties = type.GetProperties(BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.SetProperty | BindingFlags.Instance);

            // 首列为ID并作为索引。
            string propertyInfoList = "ID CHARACTER(50),";
            int columnCount = 0;

            foreach (var propertyInfo in properties)
            {
                propertyInfoList += propertyInfo.Name + " ";

                if (propertyInfo.PropertyType.Equals(typeof(double)))
                {
                    propertyInfoList += "DOUBLE" + ",";
                    columnCount++;
                }
                else if (propertyInfo.PropertyType.Equals(typeof(bool)))
                {
                    propertyInfoList += "BOOLEAN" + ",";
                    columnCount++;
                }
                else if (propertyInfo.PropertyType.Equals(typeof(int)))
                {
                    propertyInfoList += "INT" + ",";
                    columnCount++;
                }
            }

            _tableInfoList.Add(new TableInfo() { TableName = tableName, Type = type, ColumnCount = columnCount });

            // 移除最后的逗号。
            propertyInfoList = propertyInfoList.Remove(propertyInfoList.Length - 1);
            string commandString = $"CREATE TABLE '{tableName}' ({propertyInfoList});";

            ExecuteAction(commandString);
        }

        /// <summary>
        /// 添加数据至表格。
        /// </summary>
        /// <param name="tableInfo">表格信息。</param>
        /// <param name="data">需要添加的表格数据。</param>
        private void AddDataToTable(TableInfo tableInfo, object data)
        {
            var proerties = tableInfo.Type.GetProperties(BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.SetProperty | BindingFlags.Instance);
            string rowId = Guid.NewGuid().ToString("N");

            // 保证创建一个不重复的ID。
            while (tableInfo.RowIdList.Find(x => x == rowId) is not null)
            {
                rowId = Guid.NewGuid().ToString("N");
            }

            string dataList = $"'{rowId}',";

            foreach (var propertyInfo in proerties)
            {
                var propertyType = propertyInfo.PropertyType;

                if (propertyType.Equals(typeof(double))
                    || propertyType.Equals(typeof(bool))
                    || propertyType.Equals(typeof(int)))
                {
                    var value = propertyInfo.GetValue(data);
                    dataList += value?.ToString() + ",";
                }
            }

            dataList = dataList.Remove(dataList.Length - 1);
            string commandString = $"INSERT INTO '{tableInfo.TableName}' VALUES ({dataList});";

            ExecuteAction(commandString);
            tableInfo.RowIdList.Add(rowId);
        }

        /// <summary>
        /// 添加数据至表格。
        /// </summary>
        /// <typeparam name="TDataType">表格数据类型。</typeparam>
        /// <param name="tableName">表格名称</param>
        /// <param name="data">需要添加的数据。</param>
        public void AddDataToTable(string tableName, object data)
        {
            var tableInfo = GetTableInfo(tableName);
            var type = data.GetType();

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (!tableInfo.Type.Equals(type))
            {
                throw new Exception($"{type.FullName}数据类型错误，表格{tableName}数据类型为{tableInfo.Type.FullName}。");
            }

            AddDataToTable(tableInfo, data);
        }

        /// <summary>
        /// 添加一段范围的数据至表格。
        /// </summary>
        /// <typeparam name="TDataType">表格数据类型。</typeparam>
        /// <param name="tableName">表格名称。</param>
        /// <param name="dataList">需要添加表格数据列表。</param>
        public void AddDataRangeToTable<TDataType>(string tableName, List<TDataType> dataList) where TDataType : notnull
        {
            var tableInfo = GetTableInfo(tableName);
            var type = typeof(TDataType);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (dataList is null)
            {
                throw new ArgumentNullException(nameof(dataList));
            }

            if (!tableInfo.Type.Equals(type))
            {
                throw new Exception($"{type.FullName}数据类型错误，表格{tableName}数据类型为{tableInfo.Type.FullName}。");
            }

            foreach (var data in dataList)
            {
                AddDataToTable(tableInfo, data);
            }
        }

        /// <summary>
        /// 从表格中删除指定行。
        /// </summary>
        /// <param name="tableInfo">表格信息。</param>
        /// <param name="rowIndex">行索引，也是行号。</param>
        private void DeleteDataFromTable(TableInfo tableInfo, int rowIndex)
        {
            string rowId = tableInfo.RowIdList[rowIndex - 1];
            string command = $"DELETE FROM {tableInfo.TableName} WHERE ID='{rowId}';";

            ExecuteAction(command);

            tableInfo.RowIdList.RemoveAt(rowIndex - 1);
        }

        /// <summary>
        /// 从表格中删除指定行数据。
        /// </summary>
        /// <param name="tableName">需要操作的表的名称。</param>
        /// <param name="rowIndex">需要删除指定行的行号。</param>
        public void DeleteDataFromTable(string tableName, int rowIndex)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (rowIndex < 1 || rowIndex > tableInfo.RowCount)
            {
                throw new ArgumentOutOfRangeException(nameof(rowIndex));
            }

            DeleteDataFromTable(tableInfo, rowIndex);
        }

        /// <summary>
        /// 从表格中删除首行，如果表格没有任何行，则不进行任何操作。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        public void DeleteDataFromTableAtFirstRow(string tableName)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (tableInfo.RowCount <= 0)
            {
                return;
            }

            DeleteDataFromTable(tableInfo, 1);
        }

        /// <summary>
        /// 从表格中读取数据。
        /// </summary>
        /// <param name="tableInfo">表格信息。</param>
        /// <param name="rowIndex">需要读取的行号。</param>
        /// <returns>返回需要读取的行的数据。</returns>
        private object? ReadDataFromTable(TableInfo tableInfo, int rowIndex)
        {
            string rowId = tableInfo.RowIdList[rowIndex - 1];
            string commandString = $"SELECT * FROM '{tableInfo.TableName}' WHERE ID='{rowId}';";
            var type = tableInfo.Type;
            Assembly? assembly = Assembly.GetAssembly(type);
            string? typeName = type.FullName;
            var instance = assembly!.CreateInstance(typeName!);

            var properties = type.GetProperties(BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.SetProperty | BindingFlags.Instance);

            return ExecuteRead(commandString, dataReader =>
            {
                if (dataReader.Read())
                {
                    foreach (var propertyInfo in properties)
                    {
                        var propertyType = propertyInfo.PropertyType;

                        if (propertyType.Equals(typeof(double))
                          || propertyType.Equals(typeof(bool))
                          || propertyType.Equals(typeof(int)))
                        {
                            object? value = dataReader[propertyInfo.Name];
                            propertyInfo.SetValue(instance, value);
                        }
                    }

                    return instance;
                }

                return null;
            });
        }

        /// <summary>
        /// 从表格中读取指定索引的数据。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <param name="index">行索引，第一行行索引为1。</param>
        public object? ReadDataFromTable(string tableName, int rowIndex)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (rowIndex < 1 || rowIndex > tableInfo.RowCount)
            {
                throw new ArgumentOutOfRangeException(nameof(rowIndex));
            }

            return ReadDataFromTable(tableInfo, rowIndex);
        }

        /// <summary>
        /// 读取表格最后一行数据。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <returns>最后一行数据。</returns>
        public object? ReadDataFromTableAtFirstRow(string tableName)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (tableInfo.RowIdList.Count <= 0)
            {
                return null;
            }

            return ReadDataFromTable(tableInfo, 1);
        }

        /// <summary>
        /// 读取表格最后一行数据。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <returns>最后一行数据。</returns>
        public object? ReadDataFromTableAtLastRow(string tableName)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (tableInfo.RowIdList.Count <= 0)
            {
                return null;
            }

            return ReadDataFromTable(tableInfo, tableInfo.RowIdList.Count);
        }

        /// <summary>
        /// 从rowIndex行读取count行数据。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="firstIndex"></param>
        /// <param name="count"></param>
        public List<object?> ReadDataFromTable(string tableName, int rowIndex, int count)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (rowIndex < 1 || rowIndex > tableInfo.RowCount)
            {
                throw new ArgumentOutOfRangeException(nameof(rowIndex));
            }

            int lastRowIndex = rowIndex + count;

            if (lastRowIndex > tableInfo.RowCount)
            {
                throw new ArgumentOutOfRangeException(nameof(count));
            }

            List<object?> dataList = new List<object?>();

            for (int k = rowIndex; k <= lastRowIndex; k++)
            {
                dataList.Add(ReadDataFromTable(tableInfo, rowIndex));
            }

            return dataList;
        }

        /// <summary>
        /// 根据表格名称获取表格信息。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <returns></returns>
        private TableInfo? GetTableInfo(string tableName)
        {
            TableInfo? tableInfo = _tableInfoList.Find(x => x.TableName.Equals(tableName));

            return tableInfo;
        }

        /// <summary>
        /// 获取指定表格的行数。
        /// </summary>
        /// <param name="tableName">需要获取行数的表格的名称。</param>
        /// <returns>返回表格行数。</returns>
        public int GetTableRowCount(string tableName)
        {
            TableInfo? tableInfo = _tableInfoList.Find(x => x.TableName.Equals(tableName));

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            return tableInfo.RowCount;
        }

        /// <summary>
        /// 获取指定表格的列数。
        /// </summary>
        /// <param name="tableName">需要获取行数的表格的名称。</param>
        /// <returns>返回表格列数。</returns>
        public int GetTableColumnCount(string tableName)
        {
            TableInfo? tableInfo = _tableInfoList.Find(x => x.TableName.Equals(tableName));

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            return tableInfo.ColumnCount;
        }

        /// <summary>
        /// 执行读取数据。
        /// </summary>
        /// <param name="readCommand"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        private object? ExecuteRead(string readCommand, Func<SQLiteDataReader, object?> func)
        {
            _sqliteCommand.CommandText = readCommand;

            using SQLiteDataReader dataReader = _sqliteCommand.ExecuteReader();
            object? data = null;

            if (func is not null)
            {
                data = func.Invoke(dataReader);
            }

            dataReader.Close();

            return data;
        }

        /// <summary>
        /// 执行一个操作命令。
        /// </summary>
        /// <param name="actionCommand">命令字符串。</param>
        private void ExecuteAction(string actionCommand)
        {
            _sqliteCommand.CommandText = actionCommand;
            _sqliteCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 创建一个数据库源文件。
        /// </summary>
        /// <returns></returns>
        private string CreateTempDataSource()
        {
            var tempDataSource = Path.GetRandomFileName() + ".db";

            // 保证创建一个不重复的文件。
            while (File.Exists(tempDataSource))
            {
                tempDataSource = Path.GetRandomFileName() + ".db";
            }

            return tempDataSource;
        }
    }
}
